﻿using Ongoal.Quotation.Common;
using Ongoal.Quotation.Common;
using Ongoal.Quotation.Service;
using Ongoal.Quotation.Service.Common;
using Ongoal.Quotation.Service.Model.basemodel;
using Ongoal.Quotation.Service.Model.Sys;
using Ongoal.Quotation.Service.Param;
using Ongoal.Quotation.Service.ViewModel;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;

namespace Ongoal.Quotation.Service
{
    public class TableInfoService : ServiceBase, ITableInfoService
    {

        #region    创建表元数据
        public void saveTableinfo(TableInfoView viewModel)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                if (!string.IsNullOrEmpty(viewModel.TableText))
                {
                    viewModel.TableText = viewModel.TableText.Trim();
                }
                if (!string.IsNullOrEmpty(viewModel.TableCode))
                {
                    viewModel.TableCode = viewModel.TableCode.Trim();
                }
                //验证表名
                Check(string.IsNullOrEmpty(viewModel.TableText) || string.IsNullOrEmpty(viewModel.TableCode), "表名或者实体类名必须填一个");
                //验证属性
                viewModel.Fileds = viewModel.Fileds
        .Where(it => !string.IsNullOrEmpty(it.FiledName) || !string.IsNullOrEmpty(it.FiledCode)).ToList();
                Check(viewModel.Fileds.Count == 0, "请配置实体属性");
                var dbTable = new T_TableInfo
                {
                    SourceID = viewModel.SourceID,
                    CopyType = viewModel.CopyType,
                    CreateTime = viewModel.CreateTime,
                    CreatorID = viewModel.CreatorID,
                    ID = viewModel.ID,
                    ModifyID = this.CurrentUser.UserID,
                    ModifyTime = DateTime.Now,
                    PartitionType = viewModel.PartitionType,
                    Remark = viewModel.Remark,
                    TableCode = viewModel.TableCode,
                    TableText = viewModel.TableText,
                    Islevel = Convert.ToInt32(viewModel.Islevel)

                };
                //name与CODE互相转换
                AutoFillTable(dbTable);
                var dbColumns = new List<R_Table_Filed_Reference>();
                foreach (var item in viewModel.Fileds)
                {
                    R_Table_Filed_Reference entity = new R_Table_Filed_Reference
                    {
                        DataType = item.DataType,
                        CreateTime = item.CreateTime,
                        CreatorID = this.CurrentUser.UserID,
                        Remark = item.Remark,
                        FiledCode = item.FiledCode,
                        FiledLength = item.FiledLength,
                        FiledName = item.FiledName,
                        ForeignFiledID = item.ForeignFiledID,
                        VerifyRegular = item.VerifyRegular,
                        ForeignLableFiled = item.ForeignLableFiled,
                        ForeignTableID = item.ForeignTableID,
                        ID = item.ID,
                        IsForeign = Convert.ToInt32(item.IsForeign),
                        ModifyID = this.CurrentUser.UserID,
                        ModifyTime = DateTime.Now,
                        TableID = item.TableID,
                        Nullable = Convert.ToInt32(item.Nullable),
                        IsIdentity = Convert.ToInt32(item.IsIdentity),
                        IsPrimaryKey = Convert.ToInt32(item.IsPrimaryKey),
                        IsLikeFiled = Convert.ToInt32(item.IsLikeFiled),
                        CodeTable = item.CodeTable

                    };
                    dbColumns.Add(entity);


                }

                AutoFillColumns(dbColumns);
                //新增
                if (viewModel.ID == 0)
                {
                    dbTable.CreatorID = this.CurrentUser.UserID;
                    dbTable.CreateTime = DateTime.Now;
                    CheckAddName(viewModel, db);
                    var id = db.Insertable<T_TableInfo>(dbTable).ExecuteReturnIdentity();
                    foreach (var item in dbColumns)
                    {
                        item.TableID = id;
                        item.CreateTime = DateTime.Now;
                        item.CreatorID = this.CurrentUser.UserID;
                        item.ModifyID = this.CurrentUser.UserID;
                        item.ModifyTime = DateTime.Now;
                        item.FiledCode = GetColumnName(item.FiledCode);
                    }

                    db.Insertable<R_Table_Filed_Reference>(dbColumns).ExecuteCommand();
                }

                //修改
                else
                {
                    CheckUpdateName(viewModel, db);
                    db.Updateable<T_TableInfo>(dbTable).ExecuteCommand();
                    foreach (var item in dbColumns)
                    {
                        item.TableID = dbTable.ID;
                        item.ModifyTime = DateTime.Now;
                        item.ModifyID = this.CurrentUser.UserID;
                        item.FiledCode = GetColumnName(item.FiledCode);
                    }
                    var oldIds = db.Queryable<R_Table_Filed_Reference>().Where(z => z.TableID == dbTable.ID).Select(it => it.ID).ToList();
                    var delIds = oldIds.Where(it => !dbColumns.Select(y => y.ID).Contains(it)).ToList();
                    db.Deleteable<R_Table_Filed_Reference>().Where(z => (delIds.Select(it => (object)it).Contains(z.ID))).ExecuteCommand();
                    var updateColumns = dbColumns.Where(it => it.ID > 0).ToList();
                    if (updateColumns.Count() > 0)
                    {
                        db.Updateable<R_Table_Filed_Reference>(updateColumns).ExecuteCommand();
                    }

                    var insertColumns = dbColumns.Where(it => it.ID == 0).ToList();
                    if (insertColumns.Count() > 0)
                    {
                        db.Insertable<R_Table_Filed_Reference>(insertColumns).ExecuteCommand();
                    }
                }
            }

        }
        private string GetColumnName(string dbColumnName)
        {
            if (dbColumnName == null)
                return null;
            if (Regex.IsMatch(dbColumnName, @"\[.+\]"))
            {
                return Regex.Replace(dbColumnName, @"\[.+\]", "");
            }
            return dbColumnName;
        }
        private void CheckAddName(TableInfoView viewModel, SqlSugar.SqlSugarClient db)
        {
            var exit = db.Queryable<T_TableInfo>().Where(z => z.TableCode == viewModel.TableCode && z.SourceID == viewModel.SourceID).ToList().FirstOrDefault();
            Check(exit != null ? true : false, viewModel.TableCode + "表名已存在");
        }
        private void CheckUpdateName(TableInfoView viewModel, SqlSugar.SqlSugarClient db)
        {

            var exit = db.Queryable<T_TableInfo>().Where(z => z.TableCode == viewModel.TableCode && z.ID != viewModel.ID && z.SourceID == viewModel.SourceID).ToList().FirstOrDefault();
            Check(exit != null ? true : false, viewModel.TableCode + "表名已存在");
        }
        private void CheckClassName(TableInfoView viewModel)
        {
            var First = viewModel.TableCode.First().ToString();
            if (Regex.IsMatch(First, @"\d"))
            {
                new Exception(viewModel.TableCode + "不是有效类名");
            }
        }
        private void AutoFillTable(T_TableInfo dbTable)
        {
            if (string.IsNullOrEmpty(dbTable.TableText))
            {
                dbTable.TableText = dbTable.TableCode;
            }
            if (string.IsNullOrEmpty(dbTable.TableCode))
            {
                dbTable.TableCode = dbTable.TableText;
            }
        }
        private void AutoFillColumns(List<R_Table_Filed_Reference> dbFileds)
        {
            foreach (var item in dbFileds)
            {
                if (string.IsNullOrEmpty(item.FiledName))
                {
                    item.FiledName = item.FiledCode;
                }
                if (string.IsNullOrEmpty(item.FiledCode))
                {
                    item.FiledCode = item.FiledName;
                }
            }
        }
        protected void Check(bool isOk, string message)
        {
            if (isOk)
                throw new Exception(message);
        }
        #endregion

        #region 删除表元数据
        public void deleteTableinfolist(List<TableInfoView> listtable)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                List<int> Ids = listtable.Select(t => t.ID).ToList();
                //删除字段
                db.Deleteable<R_Table_Filed_Reference>().Where(z => Ids.Contains(z.TableID)).ExecuteCommand();
                //删除表
                db.Deleteable<T_TableInfo>().Where(z => Ids.Contains(z.ID)).ExecuteCommand();
            }
        }

        #endregion

        #region 查询表元数据
        public object selecttableview(PaginationData PaginationEntity, string keyword, int sourseId)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                var ResultList = db.Queryable<T_TableInfo, T_DataSource>((t, z) => new object[] {
          JoinType.Left,t.SourceID==z.ID
          }).WhereIF(sourseId > 0, (t, z) => t.SourceID == sourseId).WhereIF(!string.IsNullOrEmpty(keyword), (t, z) => t.TableCode.Contains(keyword) || t.TableText.Contains(keyword) || t.Remark.Contains(keyword)).Select((t, z) => new
          {
              ID = t.ID,
              TableID = t.ID,
              SourseID = t.SourceID,
              TableCode = t.TableCode,
              TableText = t.TableText,
              SourseName = z.Name,
              database = Convert.ToInt32(z.DatabaseType),
              PartitionType = t.PartitionType,
              CopyType = t.CopyType,
              Remark = t.Remark,
              CreateTime = t.CreateTime,
              CreatorID = t.CreatorID,
              Islevel = t.Islevel
          });
                PaginationEntity.TotalRecordNum = ResultList.Count();
                PaginationEntity.Data = ResultList.OrderBy(t => t.CreateTime, OrderByType.Desc).ToPageList(PaginationEntity.PageIndex, PaginationEntity.PageSize).Select((t) => new
                {
                    ID = t.ID,
                    TableID = t.TableID,
                    SourseID = t.SourseID,
                    TableCode = t.TableCode,
                    TableText = t.TableText,
                    SourseName = t.SourseName,
                    database = Enum.GetName(typeof(SqlSugar.DbType), t.database),
                    PartitionType = t.PartitionType,
                    CopyType = t.CopyType,
                    Remark = t.Remark,
                    CreateTime = t.CreateTime,
                    CreatorID = t.CreatorID,
                    Islevel = Convert.ToBoolean(t.Islevel)
                });

                return PaginationEntity;
            }

        }

        /// <summary>
        /// 根据表ID获取字段信息
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public object GetFieldinfo(int id)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                return db.Queryable<R_Table_Filed_Reference>()
            .LeftJoin<T_TableInfo>((z, cus) => z.ForeignTableID == cus.ID)//多个条件用&&
            .Where(z => z.TableID == id)
            .Select((z, cus) => new TableFiledView
            {
                CreateTime = z.CreateTime,
                CreatorID = z.CreatorID,
                DataType = z.DataType,
                FiledCode = z.FiledCode,
                FiledLength = z.FiledLength,
                FiledName = z.FiledName,
                ForeignFiledID = z.ForeignFiledID,
                ForeignTableID = z.ForeignTableID,
                ForeignLableFiled = z.ForeignLableFiled,
                ForeignTableName = cus.TableCode,
                ForeignFiledName = SqlFunc.Subqueryable<R_Table_Filed_Reference>().Where(t => t.ID == z.ForeignFiledID).Select(t => t.FiledName),
                ForeignLableName = SqlFunc.Subqueryable<R_Table_Filed_Reference>().Where(t => t.ID == z.ForeignLableFiled).Select(t => t.FiledName),
                ID = z.ID,
                IsForeign = Convert.ToBoolean(z.IsForeign),
                IsIdentity = Convert.ToBoolean(z.IsIdentity),
                IsLikeFiled = Convert.ToBoolean(z.IsLikeFiled),
                IsPrimaryKey = Convert.ToBoolean(z.IsPrimaryKey),
                ModifyID = z.ModifyID.Value,
                ModifyTime = z.ModifyTime.Value,
                Nullable = Convert.ToBoolean(z.Nullable),
                Remark = z.Remark,
                TableID = z.TableID,
                CodeTable = z.CodeTable,
                VerifyRegular = z.VerifyRegular
            }).ToList();
            }
        }

        /// <summary>
        /// 根据表名获取字段信息
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public object GetFieldinfoByName(string name)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {

                return db.Queryable<T_TableInfo, R_Table_Filed_Reference>((s, z) => new object[] { JoinType.Left, s.ID == z.TableID }).Where((s, z) => s.TableCode == name).Select((s, z) => new TableFiledView
                {
                    CreateTime = z.CreateTime,
                    CreatorID = z.CreatorID,
                    DataType = z.DataType,
                    FiledCode = z.FiledCode,
                    FiledLength = z.FiledLength,
                    FiledName = z.FiledName,
                    ForeignFiledID = z.ForeignFiledID,
                    ForeignTableID = z.ForeignTableID,
                    ID = z.ID,
                    IsForeign = Convert.ToBoolean(z.IsForeign),
                    IsIdentity = Convert.ToBoolean(z.IsIdentity),
                    IsLikeFiled = Convert.ToBoolean(z.IsLikeFiled),
                    IsPrimaryKey = Convert.ToBoolean(z.IsPrimaryKey),
                    ModifyID = z.ModifyID.Value,
                    ModifyTime = z.ModifyTime.Value,
                    Nullable = Convert.ToBoolean(z.Nullable),
                    Remark = z.Remark,
                    TableID = z.TableID,
                    CodeTable = z.CodeTable,
                    VerifyRegular = z.VerifyRegular
                }).ToList();
            }
        }
        #endregion

        #region 生成表
        public void CreateTables(List<TableInfoView> tableList, int dbid)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                SqlSugarClient instance = new SqlsugarCommon().GetTryDb(dbid, db);
                DbType dbtype = instance.CurrentConnectionConfig.DbType;
                List<CodeType> codetypes = db.Queryable<CodeType>().ToList();
                List<EntitiesGen> genList = GetGenList(tableList, codetypes, dbtype, instance);
                Console.WriteLine(Newtonsoft.Json.JsonConvert.SerializeObject(genList));
                foreach (var item in genList)
                {
                    item.PropertyGens = item.PropertyGens.Where(it => it.IsIgnore == false).ToList();
                    foreach (var property in item.PropertyGens)
                    {
                        if (property.IsSpecialType)
                        {
                            property.Type = "string";
                        }
                    }
                }
                string key = TemplateHelper.EntityKey + SyntaxTreeHelper.TemplateString.GetHashCode();
                foreach (var item in genList)
                {
                    var classString = TemplateHelper.GetTemplateValue(key, SyntaxTreeHelper.TemplateString, item);
                    db.Deleteable<T_Entitytable>().Where(z => z.TableName == item.TableName).ExecuteCommand();
                    //保存实体字符串
                    db.Insertable<T_Entitytable>(new T_Entitytable
                    {
                        TableName = item.TableName,
                        EntityText = classString
                    }).ExecuteCommand();
                    var types = SyntaxTreeHelper.GetModelTypeByClass(classString, item.ClassName);
                    instance.CurrentConnectionConfig.ConfigureExternalServices = new ConfigureExternalServices()
                    {
                        EntityNameService = (type, info) =>
                        {
                            if (info.EntityName == item.ClassName || (info.EntityName == null && info.DbTableName == item.ClassName))
                            {
                                info.EntityName = item.ClassName;
                                info.DbTableName = item.TableName;
                                info.TableDescription = item.Description;
                            }
                        },
                        EntityService = (type, info) =>
                        {
                            if (info.EntityName == item.ClassName)
                            {
                                var column = item.PropertyGens.FirstOrDefault(it => it.PropertyName == info.PropertyName);
                                info.DbColumnName = column.DbColumnName;
                                info.ColumnDescription = column.Description;
                                info.IsNullable = column.IsNullable;
                                info.Length = Convert.ToInt32(column.Length);
                                info.DecimalDigits = Convert.ToInt32(column.DecimalDigits);
                                info.IsPrimarykey = column.IsPrimaryKey;
                                info.IsIdentity = column.IsIdentity;
                                info.IsIgnore = column.IsIgnore;
                                info.DataType = column.DbType;
                                if (instance.CurrentConnectionConfig.DbType == DbType.Sqlite && info.IsIdentity)
                                {
                                    info.DataType = "integer";
                                }
                            }
                        }
                    };
                    instance.CodeFirst.InitTables(types);
                }

            }
        }
        private List<EntitiesGen> GetGenList(List<TableInfoView> tableList, List<CodeType> codetypes, SqlSugar.DbType databasedbType, SqlSugarClient instance)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                List<EntitiesGen> result = new List<EntitiesGen>();
                if (databasedbType == DbType.MySql)
                {
                    var timestamp = codetypes.FirstOrDefault(it => it.Name == "timestamp");
                    if (timestamp != null)
                    {
                        timestamp.CSharepType = "DateTime";
                    }
                    foreach (var item in tableList)
                    {
                        EntitiesGen gen = new EntitiesGen()
                        {
                            ClassName = item.TableText,
                            Description = item.Remark,
                            TableName = item.TableCode,
                            PropertyGens = new List<PropertyGen>()
                        };
                        foreach (var column in db.Queryable<R_Table_Filed_Reference>().Where(z => z.TableID == item.ID).ToList())
                        {
                            var codeType = codetypes.First(it => it.ID == column.DataType);
                            if (codeType.ID == 3)
                            {
                                PropertyGen proGen = new PropertyGen()
                                {
                                    DbColumnName = column.FiledCode,
                                    Description = column.Remark,
                                    IsIdentity = column.IsIdentity == 1 ? true : false,
                                    IsPrimaryKey = column.IsPrimaryKey == 1 ? true : false,
                                    PropertyName = column.FiledCode,
                                    Type = GetType(column),
                                    IsNullable = column.Nullable == 1 ? false : true,
                                    DbType = "",
                                    Length = 0,
                                    DecimalDigits = 0,
                                    IsIgnore = true,
                                    CodeType = codeType.Name

                                };
                                //var mappings = mapping.Where(it => item.DbId == it.DbId && it.TableName == item.ClassName && it.ColumnName == column.ClassProperName).ToList(); ;
                                //proGen.MappingProperties = tags.Where(it => mappings.Any(x => x.TagId == it.Id + "")).ToList();
                                gen.PropertyGens.Add(proGen);
                            }
                            else
                            {
                                var dbType = GetTypeInfoByDatabaseType(Newtonsoft.Json.JsonConvert.DeserializeObject<DbTypeInfo[]>(codeType.DbType), databasedbType);
                                PropertyGen proGen = new PropertyGen()
                                {
                                    DbColumnName = column.FiledCode,
                                    Description = column.Remark,
                                    IsIdentity = column.IsIdentity == 1 ? true : false,
                                    IsPrimaryKey = column.IsPrimaryKey == 1 ? true : false,
                                    PropertyName = column.FiledCode,
                                    Type = IsSpecialType(column) ? GetType(column) : codeType.CSharepType,
                                    IsNullable = column.Nullable == 1 ? false : true,
                                    DbType = dbType.Name,
                                    Length = dbType.Length,
                                    DecimalDigits = dbType.DecimalDigits,
                                    IsSpecialType = IsSpecialType(column),
                                    CodeType = codeType.Name,
                                    DefaultValue = null
                                };
                                gen.PropertyGens.Add(proGen);
                            }
                        }
                        result.Add(gen);
                    }
                    return result;
                }

                return result;
            }
        }
        private static string GetType(R_Table_Filed_Reference column)
        {
            string type = "string";
            if (IsSpecialType(column))
            {
                type = Regex.Match(column.FiledCode, @"\[(.+)\]").Groups[1].Value;
            }

            return type;
        }
        private static bool IsSpecialType(R_Table_Filed_Reference column)
        {
            return Regex.IsMatch(column.FiledCode, @"\[.+\]");
        }

        private DbTypeInfo GetTypeInfoByDatabaseType(DbTypeInfo[] dbType, DbType databasedbType)
        {
            DbTypeInfo result = dbType.First();
            List<string> mstypes = new List<string>();
            switch (databasedbType)
            {
                case DbType.MySql:
                    mstypes = SqlSugar.MySqlDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.SqlServer:
                    mstypes = SqlSugar.SqlServerDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.Sqlite:
                    mstypes = SqlSugar.SqliteDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.Oracle:
                    mstypes = SqlSugar.OracleDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.PostgreSQL:
                    mstypes = SqlSugar.PostgreSQLDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                case DbType.Dm:
                    mstypes = SqlSugar.DmDbBind.MappingTypesConst.Select(it => it.Key.ToLower()).ToList();
                    break;
                default:
                    break;
            }
            result = dbType.FirstOrDefault(it => mstypes.Contains(it.Name.ToLower()));
            if (result == null)
            {
                throw new Exception("WebFirst暂时不支持类型" + string.Join(",", dbType.Select(it => it.Name)));
            }
            return result;
        }
        #endregion

        #region 导入表
        public bool importtable(List<V_Tableinfo> tablelist, int dbid)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                SqlSugarClient tableDb = new SqlsugarCommon().GetTryDb(dbid, db);
                var type = db.Queryable<CodeType>().ToList();
                var entityList = db.Queryable<T_TableInfo>().Where(z => z.SourceID == dbid).ToList();
                db.Ado.BeginTran();
                try
                {
                    List<T_TableInfo> Inserts = new List<T_TableInfo>();
                    foreach (var item in tablelist)
                    {
                        TableInfoView code = new TableInfoView()
                        {
                            TableCode = item.Name,
                            TableText = item.Name,
                            SourceID = dbid,
                            Fileds = new List<TableFiledView>()
                        };
                        var entity = entityList.FirstOrDefault(it => it.TableCode.Equals(item.Name, StringComparison.OrdinalIgnoreCase));
                        if (entity == null)
                        {
                            entity = new T_TableInfo();
                        }
                        foreach (var columnInfo in tableDb.DbMaintenance.GetColumnInfosByTableName(item.Name, false))
                        {
                            CodetableImport t = new CodetableImport();
                            var typeInfo = t.GetEntityType(type, columnInfo, tableDb.CurrentConnectionConfig.DbType);
                            TableFiledView column = new TableFiledView()
                            {
                                FiledCode = columnInfo.DbColumnName,
                                FiledName = columnInfo.DbColumnName,
                                Remark = columnInfo.ColumnDescription,
                                IsIdentity = columnInfo.IsIdentity,
                                IsPrimaryKey = columnInfo.IsPrimarykey,
                                Nullable = columnInfo.IsNullable == false,
                                TableID = entity.ID,
                                DataType = typeInfo.CodeType.ID,
                                FiledLength = Convert.ToInt32(typeInfo.DbTypeInfo.Length),
                            };
                            code.Fileds.Add(column);
                        }
                        saveTableinfo(code);
                    };
                    db.Ado.CommitTran();

                }
                catch (Exception ex)
                {
                    db.Ado.RollbackTran();
                    throw new Exception(ex.Message);
                }
                return true;
            }

        }
        #endregion

        #region 基础操作
        /// <summary>
        /// 删除表数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public bool deletetableview(int id)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                try
                {


                    db.Ado.BeginTran();
                    db.Deleteable<T_TableInfo>(z => z.ID == id).ExecuteCommand();
                    db.Deleteable<R_Table_Filed_Reference>(z => z.TableID == id).ExecuteCommand();
                    db.Ado.CommitTran();
                    return true;
                }
                catch (Exception e)
                {

                    throw new Exception(e.Message);
                };
            }
        }


        /// <summary>
        /// 显示实体字符串
        /// </summary>
        /// <returns></returns>
        public object ShowEntity(string tablename)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                try
                {
                    var result = db.Queryable<T_Entitytable>().Where(z => z.TableName == tablename).First();
                    if (result == null)
                    {
                        return "";
                    }
                    return result;
                }
                catch (Exception e)
                {

                    throw new Exception(e.Message);
                };
            }
        }

        //获取数据库中所有表结构
        public object Gettablelist(int dbid, string name)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                SqlSugarClient instance = new SqlsugarCommon().GetTryDb(dbid, db);
                var table = instance.DbMaintenance.GetTableInfoList(false).Select(z => new V_Tableinfo
                {
                    Name = z.Name,
                    Description = z.Description,
                    DbObjectType = z.DbObjectType
                });
                var TableCodelist = db.Queryable<T_TableInfo>().Select(z => z.TableCode).ToList();
                table = table.Where(z => !TableCodelist.Contains(z.Name)).ToList();
                if (!string.IsNullOrEmpty(name))
                {
                    return table.Where(z => z.Name.ToLower().Contains(name.ToLower())).ToList();
                }
                else
                {
                    return table;
                }
            }
        }
        #endregion

        #region 表格界面管理
        /// <summary>
        /// 获取列表
        /// </summary>
        /// <param name="keyword"></param>
        /// <returns></returns>
        public object GetInterfaceTable(string keyword)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                return db.Queryable<T_Sys_InterfaceTable>().WhereIF(!string.IsNullOrEmpty(keyword), z => z.TableName.Contains(keyword) || z.TableCNName.Contains(keyword)).ToList().Select(z => new
                {
                    ID = z.ID,
                    CreateTime = z.CreateTime,
                    CreatorID = z.CreatorID,
                    ModifyID = z.ModifyID,
                    ModifyTime = z.ModifyTime,
                    TableCNName = z.TableCNName,
                    TableID = z.TableID,
                    TableName = z.TableName,
                    ShowName = z.TableName + "(" + z.TableCNName + ")"
                });
            }
        }
        /// <summary>
        /// 获取显示信息
        /// </summary>
        /// <param name="Interfacetableid">表ID</param>
        /// <returns></returns>
        public object GetInterfacesetting(int Interfacetableid)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                try
                {
                    db.Ado.BeginTran();
                    List<string> notinview = typeof(TreeBase<dynamic>).GetProperties().Select(t => t.Name).ToList(); //new List<string> { "ModifyID","ID", "ModifyTime", "CreatorID", "CreateTime", "ParentID", "PropPath", "NodeLevel" }; 
                    List<T_Sys_Interfacesettings> setlist = new List<T_Sys_Interfacesettings>();
                    var TableID = db.Queryable<T_Sys_InterfaceTable>().Where(t => t.ID == Interfacetableid).Select(z => z.TableID).First();
                    var fieldlist = db.Queryable<R_Table_Filed_Reference>().Where(z => !notinview.Contains(z.FiledCode)).Where(z => z.TableID == TableID).ToList();
                    var seq = 0;
                    foreach (var field in fieldlist)
                    {
                        var info = db.Queryable<T_Sys_Interfacesettings>().Where(t => t.InterfaceTableID == Interfacetableid && t.FieldID == field.ID).First();
                        if (info == null)
                        {
                            T_Sys_Interfacesettings model = new T_Sys_Interfacesettings
                            {
                                Showlabel = field.Remark,
                                Showwidth = 150,
                                SoursedataCode = "",
                                SoursedataType = 0,
                                TableID = TableID,
                                Seq = seq,
                                FieldID = field.ID,
                                FieldName = field.FiledCode,
                                FormType = 1701,
                                InputType = "text",
                                InterfaceTableID = Interfacetableid,
                                RegxValid = field.VerifyRegular,
                                CreateTime = DateTime.Now,
                                CreatorID = CurrentUser.UserID,
                                ModifyID = CurrentUser.UserID,
                                ModifyTime = DateTime.Now
                            };
                            setlist.Add(model);
                            seq++;
                        }
                    }
                    db.Insertable<T_Sys_Interfacesettings>(setlist).ExecuteCommand();
                    db.Ado.CommitTran();
                    return db.Queryable<T_Sys_Interfacesettings>().Where(z => z.InterfaceTableID == Interfacetableid).ToList();
                }
                catch (Exception e)
                {
                    db.Ado.RollbackTran();
                    throw new Exception(e.Message);
                }
            }
        }

        /// <summary>
        /// 获取没有导入表格的数据
        /// </summary>
        /// <returns></returns>
        public object GetNotleadinTables(string keyword)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                var Ids = db.Queryable<T_Sys_InterfaceTable>().Select(z => z.TableID).ToList();
                return db.Queryable<T_TableInfo>().Where(z => !Ids.Contains(z.ID)).WhereIF(!string.IsNullOrEmpty(keyword), z => z.TableCode.Contains(keyword) || z.TableText.Contains(keyword) || z.Remark.Contains(keyword)).ToList();
            }
        }
        /// <summary>
        /// 导入
        /// </summary>
        /// <returns></returns>
        public object leadinterface(List<T_TableInfo> tablelist)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                try
                {

                    db.Ado.BeginTran();
                    List<string> notinview = typeof(TreeBase<dynamic>).GetProperties().Select(t => t.Name).ToList(); //new List<string> { "ModifyID","ID", "ModifyTime", "CreatorID", "CreateTime", "ParentID", "PropPath", "NodeLevel" }; 
                    foreach (var item in tablelist)
                    {
                        T_Sys_InterfaceTable table = new T_Sys_InterfaceTable()
                        {
                            TableCNName = item.Remark,
                            TableID = item.ID,
                            TableName = item.TableCode
                        };
                        var fieldlist = db.Queryable<R_Table_Filed_Reference>().Where(z => !notinview.Contains(z.FiledCode)).Where(z => z.TableID == item.ID).ToList();
                        var entity = this.Insert<T_Sys_InterfaceTable>(table);
                        if (entity != null)
                        {
                            var InterfaceTableID = entity.ID;
                            List<T_Sys_Interfacesettings> setlist = new List<T_Sys_Interfacesettings>();
                            var seq = 0;
                            foreach (var field in fieldlist)
                            {

                                T_Sys_Interfacesettings model = new T_Sys_Interfacesettings
                                {
                                    Showlabel = field.Remark,
                                    Showwidth = 150,
                                    SoursedataCode = "",
                                    SoursedataType = 0,
                                    TableID = item.ID,
                                    Seq = seq,
                                    FieldID = field.ID,
                                    FieldName = field.FiledCode,
                                    FormType = 1,
                                    InterfaceTableID = InterfaceTableID,
                                    RegxValid = field.VerifyRegular,
                                    CreateTime = DateTime.Now,
                                    CreatorID = CurrentUser.UserID,
                                    ModifyID = CurrentUser.UserID,
                                    ModifyTime = DateTime.Now
                                };
                                setlist.Add(model);
                                seq++;
                            }
                            db.Insertable<T_Sys_Interfacesettings>(setlist).ExecuteCommand();

                        }
                    }
                    db.Ado.CommitTran();
                    return true;
                }
                catch (Exception e)
                {
                    db.Ado.RollbackTran();
                    throw new Exception(e.Message);
                }
            }
        }


        /// <summary>
        /// 同步
        /// </summary>
        /// <param name="tableid"></param>
        /// <returns></returns>
        public object overloadlead(int Interfaceid) {

            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                try
                {

                    db.Ado.BeginTran();
                    List<string> notinview = typeof(TreeBase<dynamic>).GetProperties().Select(t => t.Name).ToList();
                    //获取表ID
                        var TableId = db.Queryable<T_Sys_InterfaceTable>().Where(z => z.ID == Interfaceid).Select(z => z.TableID).First();
                    //获取表字段信息
                        var fieldlist = db.Queryable<R_Table_Filed_Reference>().Where(z => !notinview.Contains(z.FiledCode)).Where(z => z.TableID == TableId).ToList();
                        var fieldids = fieldlist.Select(z => z.ID).ToList();
                    //查询已经有的字段信息
                        var hasfieldlist=db.Queryable<T_Sys_Interfacesettings>().Where(z=>z.TableID==TableId).ToList();
                        var hasfieldids = hasfieldlist.Select(z => z.FieldID).ToList();

                     //获取没有的字段信息
                    var addfields= fieldids.Except(hasfieldids);
                    //获取多余的字段
                    var deleteids = hasfieldids.Except(fieldids);
                    //获取原有字段
                    var updatefields = fieldids.Intersect(hasfieldids);
                    //更新
                    var updatelist = hasfieldlist.Where(z => updatefields.Contains(z.FieldID)).ToList();
                    foreach (var field in updatelist)
                    {
                        field.FieldName = fieldlist.Where(z => z.ID == field.FieldID).Select(z => z.FiledCode).FirstOrDefault();
                    }
                    //更新原有的
                    db.Updateable<T_Sys_Interfacesettings>(updatelist).ExecuteCommand();

                    var addlist = fieldlist.Where(z => addfields.Contains(z.ID)).ToList();
                    //新增新加的字段
                            List<T_Sys_Interfacesettings> setlist = new List<T_Sys_Interfacesettings>();
                            foreach (var field in addlist)
                            {

                                T_Sys_Interfacesettings model = new T_Sys_Interfacesettings
                                {
                                    Showlabel = field.Remark,
                                    Showwidth = 150,
                                    SoursedataCode = "",
                                    SoursedataType = 0,
                                    TableID = TableId,
                                    Seq = 1,
                                    FieldID = field.ID,
                                    FieldName = field.FiledCode,
                                    FormType = 1,
                                    InterfaceTableID = Interfaceid,
                                    RegxValid = field.VerifyRegular,
                                    CreateTime = DateTime.Now,
                                    CreatorID = CurrentUser.UserID,
                                    ModifyID = CurrentUser.UserID,
                                    ModifyTime = DateTime.Now
                                };
                                setlist.Add(model);
        
                            }
                            db.Insertable<T_Sys_Interfacesettings>(setlist).ExecuteCommand();
                    //删除没有的字段
                    var deletelist = hasfieldlist.Where(z => deleteids.Contains(z.FieldID)).ToList();
                    //更新原有的
                    db.Deleteable<T_Sys_Interfacesettings>(deletelist).ExecuteCommand();
                    db.Ado.CommitTran();
                    return true;
                }
                catch (Exception e)
                {
                    db.Ado.RollbackTran();
                    throw new Exception(e.Message);
                }
            }
        }
        /// <summary>
        /// 编辑显示字段
        /// </summary>
        /// <param name="entitys"></param>
        /// <returns></returns>
        /// <exception cref="Exception"></exception>
        public object Editinterfacesetting(List<T_Sys_Interfacesettings> entitys)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                try
                {
                    foreach (var item in entitys)
                    {
                        var model = db.Queryable<T_Sys_Interfacesettings>().Where(z => z.ID == item.ID).First();
                        if (!DataHelper.CompareType(model, item))
                        {
                            if (item.FormType != 1702)
                            {
                                item.SoursedataType = 0;
                                item.SoursedataCode = "";
                                item.ForeignTableID = 0;
                                item.ForeignFiledID = 0;
                                item.ForeignLableFiled = 0;
                            }
                            if (item.SoursedataType != 2)
                            {
                                item.ForeignTableID = 0;
                                item.ForeignFiledID = 0;
                                item.ForeignLableFiled = 0;
                            }
                            if (item.SoursedataType != 1)
                            {
                                item.SoursedataCode = "";
                            }
                            if (item.FormType != 1701)
                            {
                                item.InputType = "";
                                item.MaxLen = 0;
                                item.MinLen = 0;
                            }
                            this.Update<T_Sys_Interfacesettings>(item);
                        }
                    }

                    return true;
                }
                catch (Exception e)
                {

                    throw new Exception(e.Message);
                }
            }
        }

        /// <summary>
        /// 根据表名获取表信息
        /// </summary>
        /// <param name="tablename"></param>
        /// <returns></returns>
        public object GetshowTableInfo(string tablename)
        {
            using (var db = Db.GetDbContext(string.Empty))
            {
                var list = db.Queryable<T_Sys_InterfaceTable, T_Sys_Interfacesettings>((x, z) => new object[] {
            JoinType.Left,x.ID==z.InterfaceTableID
            }).Where((x, z) => x.TableName == tablename).Select((x, z) => z).ToList();
                return list.OrderBy(t => t.Seq).ToList();

            }
        }

        #endregion

        #region 表与字段
        /// <summary>
        /// 获取数据库中所有表
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <returns></returns>
        public object GetTablesList(string TableName)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                var TableCodelist = db.Queryable<T_TableInfo>().ToList();
                if (!string.IsNullOrEmpty(TableName))
                {
                    return TableCodelist.Where(z => z.TableCode.ToLower().Contains(TableName.ToLower()) ||
                    z.TableText.ToLower().Contains(TableName.ToLower()) ||
                    z.Remark.Contains(TableName)).ToList();
                }
                else
                {
                    return TableCodelist;
                }
            }
        }

        /// <summary>
        /// 根据表ID获取表所有字段
        /// </summary>
        /// <param name="TableId">表ID</param>
        /// <param name="FiledName">字段名</param>
        /// <returns></returns>
        public object GetFiledsList(int TableId, string FiledName)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                return db.Queryable<R_Table_Filed_Reference>().Where(z => z.TableID == TableId).ToList();
            }
        }

        public object GetWhereTableList(PublicParam Param)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                ResultParam resultParam = new ResultParam();
                StringBuilder where = new StringBuilder();
                if (Param.WhereFiledList != null && Param.WhereFiledList.Count > 0)
                {
                    foreach (var item in Param.WhereFiledList)
                    {
                        if (item.WhereFiledValue != null && item.WhereFiledValue.ToString() != "")
                        {
                            if (item.WhereMode == "=")
                            {
                                where.AppendFormat("{0} {1}='{2}'", item.WhereCondition, item.WhereFiledName, item.WhereFiledValue);
                            }
                            else
                            {
                                where.AppendFormat("{0} {1} like '%{2}%'", item.WhereCondition, item.WhereFiledName, item.WhereFiledValue);
                            }
                        }
                    }
                }
                var wheresql = where.ToString();
                if (!string.IsNullOrEmpty(wheresql))
                {
                    wheresql = " where " + wheresql;
                }
                var sql = string.Format(" select {0} from {1} {2} order by CreateTime desc", Param.FiledName, Param.TableName, wheresql);
                List<dynamic> ResultList = new List<dynamic>();
                if (Param.IsPage)
                {
                    ResultList = db.SqlQueryable<dynamic>(sql).ToPageList(Param.PageIndex, Param.PageSize).ToList();
                }
                else
                {
                    ResultList = db.SqlQueryable<dynamic>(sql).ToList();
                }
                if (ResultList != null && ResultList.Count > 0)
                {
                    resultParam.Code = 200;
                    resultParam.Msg = "成功";
                    resultParam.Data = ResultList;
                    resultParam.PageCount = ResultList.Count;
                }
                return resultParam;
            }
        }

        /// <summary>
        /// 获取代码表
        /// </summary>
        /// <param name="Param">参数实体</param>
        /// <returns></returns>
        public object GetCodeTableList(PublicParam Param)
        {
            using (SqlSugarClient db = Db.GetDbContext(""))
            {
                ResultParam resultParam = new ResultParam();
                var ResultList = db.Queryable<T_CodeTable>();
                if (!string.IsNullOrEmpty(Param.KeyWord))
                {
                    ResultList = ResultList.Where(z => z.Name.Contains(Param.KeyWord) || z.Code.Contains(Param.KeyWord) || z.Des.Contains(Param.KeyWord));
                }
                resultParam.Data = ResultList.OrderBy(t => t.CreateTime, OrderByType.Desc).ToPageList(Param.PageIndex, Param.PageSize);
                if (resultParam.Data != null)
                {
                    resultParam.Code = 200;
                    resultParam.Msg = "成功";
                    resultParam.PageCount = ResultList.Count();
                }
                return resultParam;
            }
        }
        #endregion
    }
}
